------------------------------------------------------
------------------------------------------------------
--enter data into database
--COMMIT operations
--ROLLBACK operations
------------------------------------------------------
------------------------------------------------------

--Enter four sets of data into the Accounts Table

USE [Cycles to Share Version1]                                 --use this database
GO

INSERT INTO [dbo].[Account]  (AccountNumber,RegistrationDate,FirstName,LastNam,AccountDateOfBirth,AccountPhoneNumber,AccountAddress,AccountCityCode,AccountPostcode,AccountBikeNu)  --insert statement and the order of values

VALUES --values

(12/10/87,'John','Smith','15/04/2019',07891104049,'123_noWhereStreet','Cardiff','Sa623sj','16/04/2019',3),       --testData
(12/10/87,'Martin','Johnson','15/04/2019',07891101724,'JohnsonStreet','Manchester','DY63WLN','16/04/2019',4),    --testData
(12/10/87,'Stephan','Richards','15/04/2019',07891112579,'Hasguard Cross','London','Sy792LG','16/04/2019',1),     --testData
(12/10/87,'Mike','Donaldson','15/04/2019',0701973049,'456-Everywhere','Leicester','DY39WKF','16/04/2019',0),     --testData
(03/09/77,'Donald','Trump','01/04/2019',01573594128,'White House','Washington DC','SH48 9GF','06/09/2019',5);    --testData

PRINT 'Test data entered into the Account table successfully';       --print message when complete

--Commit operations

--Enter four sets of data into the PaymentMethods Table

USE [Cycles to Share Version1]                    -- use this database
GO

INSERT INTO [dbo].[PaymentMethods]  (AccountNumber,AccountEmail,AccountPassword,AccountPaymentDate,AccountPaymentCard,AccountPaymentCardCVC,)    --insert statement and the order of values

VALUES 

(HASHBYTES('Ashley123456@hotmail.co.uk','Password1234'),HASHBYTES('123445677894','369')),             --testData
(HASHBYTES('Martiny123456@hotmail.co.uk','Password56789')HASHBYTES('198766199716','753')),              --testData
(HASHBYTES('Stephan123456@hotmail.co.uk','Password0123')HASHBYTES('054518466781','424')),              --testData
(HASHBYTES('Mike123456@hotmail.co.uk','Password4567')HASHBYTES('06491872300','273')),                --testData
(HASHBYTES('DonaldTrump@hotmail.co.uk','Password7890')HASHBYTES('01548131870','274'));                  --testData

PRINT 'Test data entered into the PaymentMethods table succesfully';

USE [Cycles to Share Version1]
GO

DELETE from Account 
	where "FirstName" ='Donald';
Commit;

print 'Record succesfully removed, and change commited to database'

--rollback operations

USE [Cycles to Share Version1]
GO

DELETE from Account 
	where "FirstName" ='Donald';
ROLLBACK

print 'Chages have been rolled back and data successfully added back onto the database'

-- Enter Four sets of data into the AccountSub Table

USE [Cycles to Share Version1]
GO

INSERT INTO [dbo].[AccountSub] (AccountSubID,AccountSubStartDate,AccountSubEndDate,AccountSubType,AccountSubTarrifRate,AccountSubStatus)             --insert statement and the order of values
     VALUES
           ('Montly','15/04/2019','15/04/2020','Annually',19.99,'Open'),                      --testData
		   ('Montly','02/07/2019','02/0/2020','Monthly',23.99,'Open'),                         --testData
		   ('Montly','10/07/2017','10/07/2018','Annually',19.99,'Closed'),                    --testData
		   ('Montly','23/10/2019','23/10/2020','Montly',23.99,'Open');                           --testData
GO

print 'Successfullt input 4 sets of test data'

USE [Cycles to Share Version1]
GO

print 'will try to insert test values into Bike table'

INSERT INTO [dbo].[Bike](BikeStationCode,BikeSatus,BikeComments)                --insert statement and the order of values
    VALUES
           ('STN001','Onloan','Front brake is squeaky'),                           --testData
		   ('STN004','Available','Bike is OK'),                                   --testData
		   ('STN006','Onloan','Available'),                                       --testData
		   ('STN002','Onloan','chain needs new oil');	                                --testData
GO

PRINT 'Test values have been added to the Bike table successfully'

USE [Cycles to Share Version1]
GO

print 'Will try to add test values into the City table'

INSERT INTO [dbo].[City](CityName,CityCounty,CityRegion)                       --insert statement and the order of values
     VALUES
           ('Cardiff','Wales','East'),                                                --testData
		   ('London','England','City Center'),                                      --testData
		   ('Manchester','England','North'),                                              --testData
		   ('edinburgh','Scotland','City Center');                                        --testData
GO
print 'Test values successfully added to the City table'

USE [Cycles to Share Version1]
GO

print 'Will attempt to add data into the Rental Table'

INSERT INTO [dbo].[Rental](RentalAccountNum,RentalBookingDateTime,RentalPickupStation,RentalLockcode)                    --insert statement and the order of values
     VALUES
           ('Acc001','2019-10-23 21:00:00','STN001',HASHBYTES('lockcode','123456')),                            --testData
		   ('Acc008','2019-06-19 17:45:00','STN001',HASHBYTES('lockcode','234567')),                               --testData
		   ('Acc0017','2019-04-19 15:30:00','STN001',HASHBYTES('lockcode','345678')),                              --testData
		   ('Acc0099','2019-07-14 09:00:00','STN001',HASHBYTES('lockcode','4567891'));                         --testData
GO

print 'Successfully added test date to Table Rental'
USE [Cycles to Share Version1]
GO

print 'will attempt to add test data to the Table rentalBike'

INSERT INTO [dbo].[RentalBike](RentalBikeCode,RentalBikePickup,RentalReturn,RentalReturnStation,RentalHireCharge,RentalLostLockCharge,RentalDamageCharge,RentalComments)              --insert statement and the order of values

     VALUES
           ('Bike001','2019-10-23 21:00:00','£4.99','STN001','£19.99','£3.99','9.99','Customer had a refund'),                           --testData
		   ('Bike009','2019-7-27 06:30:00','£4.99','STN003','£19.99','£3.99','9.99','Customer Satisfied'),                               --testData
		   ('Bike0013','2019-9-17 3:30:00','£4.99','STN004','£19.99','£3.99','9.99','Customer Satisfied'),                              --testData
		   ('Bike021','2019-1-13 7:30:00','£4.99','STN002','£19.99','£3.99','9.99','Customer requested to speak to manager');               --testData
GO

print 'Test data successfully added to RentalBike Table'

USE [Cycles to Share Version1]
GO

print 'Will attempt to add test data into the Station table'

INSERT INTO [dbo].[Station](StationName,StationAddress,StationCityCode,StationPostcode,StationPhoneNumber,StationReginalStation)                       --insert statement and the order of values
     VALUES 
           ('London','London Main Street','DY63 WLN','STN001',07891104044,'STN001'),                                     --testData
		   ('Cardiff','Cardiff Main Street','DF48 481','STN002',04578135849,'STN002'),                                      --testData
		   ('Edinburgh','Edinburgh Main Street','EJ48 D48','STN003',01579482351,'STN003'),                                  --testData
		   ('Manchester','Manchester Main Street','WN47 48N','STN004',01547824875,'STN004');                               --testData
GO

print 'Successfully added test data into the Station Table'

USE [Cycles to Share Version1]
GO
print 'will attempt to insert test data into Table TarrifRate'

INSERT INTO [dbo].[TarrifRate](TarrifID,TarrifSubType,TarrifSubPrice,TarrifFirstHourRate,TarrifHourlyRate,TarrifMaxDailyRate)                     --insert statement and the order of values
     VALUES 
           ('Standard Daily rate','Monthly Member discount',4.99,3.99,6.99,59.99),                        --testData
		   ('Premium Daily rate','Annual Member discount',4.99,3.99,6.99,49.99),                          --testData
		   ('Standard Daily rate','Monthly Member discount',4.99,3.99,6.99,59.99),                         --testData
		   ('Discounted Daily rate','Annual Member discount',4.99,3.99,6.99,49.99);                         --testData
GO

Print 'Successfully added test date onto the TarrifRate Table'

------------------------------------------------------
------------------------------------------------------
--Indexes
------------------------------------------------------
------------------------------------------------------
USE [Cycles to Share Version1]
GO

print 'Will attempt to create an index on the bike table using the bike comments column'

DROP INDEX IXBIKECOMMENTS                  --drop index statement
ON dbo.Bike (BikeComments);                --identify which index to drop and on which table
GO

PRINT 'Old index dropped'
PRINT 'Creating new INDEX'

CREATE INDEX IX_BIKECOMMENTS                      --create new index and name the index
ON Bike (BikeComments)                            --on which table and identify the index
go

PRINT 'IXBIKECOMMENTS successfully created'              --print statement


USE [Cycles to Share Version1]
GO

DROP INDEX IXAccountFirstName                     --drop index IXAccountFirstName
ON Account (FirstNam ASC)                       --on account 
GO

PRINT 'Old index dropped'
PRINT 'Creating new INDEX'

print 'Will attempt to create an index on Account table using the customer First Name arraged into ascending order '

CREATE INDEX IX_AccountFirstName                    --create new index and name the index
  ON Account (FirstName ASC)                        --on the account table and order them Ascending
  
PRINT 'INDEX Successfully created on account using First Name sorted into Ascending order'

------------------------------------------------------
------------------------------------------------------
--Business Rules
--check constraints
------------------------------------------------------
------------------------------------------------------
  
Alter TABLE Account(
	AccountNumber int IDENTITY(1,1) PRIMARY KEY not null,
	RegistrationDate Date NOT NULL,
	FirstName nchar(20) NOT NULL,
	LastNam nchar(20) NOT NULL,
	AccountDateOfBirth nchar(20) null,
	AccountEmail nchar(20) NOT NULL UNIQUE,
	AccountPhoneNumber int NOT NULL ,
	AccountAddress nchar(20) null,FOREIGN KEY REFERENCES StationAddress(Station),
	AccountCityCode nchar(20) NOT NULL,
	AccountPostcode nchar(9) NOT NULL,
	AccountPaymentDate Date NOT NULL,
	AccountBikeNu int check (AccountBikeNu >= 6)                              --add the check constraint onto the table to add the AccountBikeNu field onto the existing table,
																			  --note that the FK PK have to be dropped to allow alteration 
);
	
PRINT 'Acount Tables created with Check Constraint applied to the AccountBikeNu limiting the total number of bike per account to 6 or less bikes'            --print statement

  